﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CinemaBookingProject.DBLayer;
using System.Threading;
using System.Configuration;

public class DBSeat
{
    static internal SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Balder"].ConnectionString);//new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E8;Persist Security Info=True;User ID=dmaa0912_E8;Password=MaaGodt");
    private static object obj = new object();

    #region Required Methods:
    /// <summary>
    /// Returns a seat object from the database.
    /// </summary>
    /// <param name="id">The ID of the seat.</param>
    /// <returns>Seat object</returns>
    public Seat Read(int id)
    {
        SqlDataReader rdr = null;
        Seat seat = null;
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from Seat where id = " + id, Connection);
            rdr = cmd.ExecuteReader();

            //Der er max ét objekt med det givne ID.
            while (rdr.Read())
            {
                seat = new Seat((int)rdr["RowNo"], 
                                (int)rdr["SeatNo"],
                                (double)rdr["Price"], 
                                (bool)rdr["Handicap"], 
                                (bool)rdr["LegSpace"], 
                                (byte)rdr["CinemaRoom"]);
                //seat = new Seat((int)rdr["Row-No"], (int)rdr["SeatNo"], (int)rdr["CinemaRoom"]);
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return seat;
    }

    public List<Seat> ReadAll(int room)
    {
        SqlDataReader rdr = null;
        List<Seat> seats = new List<Seat>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from Seat WHERE CinemaRoom = @CinemaRoom", Connection);
            cmd.Parameters.Add("CinemaRoom", SqlDbType.TinyInt).Value = room;
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                //Console.WriteLine(rdr["CinemaRoom"].GetType());
                seats.Add(new Seat((int)rdr["RowNo"],
                                (int)rdr["SeatNo"],
                                (double)rdr["Price"],
                                (bool)rdr["Handicap"],
                                (bool)rdr["LegSpace"],
                                (byte)rdr["CinemaRoom"]));

                //seat = new Seat((int)rdr["Row-No"], (int)rdr["SeatNo"], (int)rdr["CinemaRoom"]);
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return seats;
    } 
    /// <summary>
    /// Inserts a seat into the database.
    /// </summary>
    /// <param name="seat">The seat to insert.</param>
    /// <returns>-1 if sqlexception (seat already in db or wrong room), 0 if nothing happened and 1 if the seat was inserted correctly.</returns>
    public int Insert(Seat seat)
    {
        int wentWell = 0;
        string query =
            "INSERT INTO Seat (ID, RowNo, SeatNo, Handicap, LegSpace, Price, CinemaRoom) VALUES (@ID, @Row, @Seat, @Handicap, @Legspace, @Price, @Room)";
        
        bool exits = DoesExits(seat);
        if (!exits)
        {


            try
            {
                Monitor.Enter(obj);
                SqlCommand cmd = new SqlCommand(query, Connection);
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((seat.CinemaRoom * 10000) + (seat.Row * 100) + seat.SeatNr);
                cmd.Parameters.Add("@Row", SqlDbType.Int).Value = seat.Row;
                cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = seat.SeatNr;
                cmd.Parameters.Add("@Handicap", SqlDbType.Bit).Value = seat.Handicap;
                cmd.Parameters.Add("@Legspace", SqlDbType.Bit).Value = seat.LegSpace;
                cmd.Parameters.Add("@Price", SqlDbType.Float).Value = seat.Price;
                cmd.Parameters.Add("@Room", SqlDbType.TinyInt).Value = seat.CinemaRoom;

                Connection.Open();
                wentWell = cmd.ExecuteNonQuery();

            }
            //looks like it already exists in the database!
            catch (SqlException)
            {
                wentWell = -1;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Connection.Close();
                Monitor.Exit(obj);
            }
        }
        return wentWell;
    }

    
    public int Update(Seat seat)
    {
        int wentWell = 0;
        string query =
            "UPDATE Seat Handicap = @Handicap, LegSpace = @Legspace, Price = @Price WHERE ID = @ID";
        try
        {
            Monitor.Enter(obj);
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((seat.CinemaRoom * 10000) + (seat.Row * 100) + seat.SeatNr);
            cmd.Parameters.Add("@Handicap", SqlDbType.Bit).Value = seat.Handicap;
            cmd.Parameters.Add("@Legspace", SqlDbType.Bit).Value = seat.LegSpace;
            cmd.Parameters.Add("@Price", SqlDbType.Float).Value = seat.Price;

            Connection.Open();
            wentWell = cmd.ExecuteNonQuery();
             
        }
        //looks like it already exists in the database!
        catch (SqlException)
        {
            wentWell = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            Connection.Close();
            Monitor.Exit(obj);
            
        }
        return wentWell;
    }

    public int Delete(Seat seat)
    {
        int wentWell = 0;
        string query =
            "DELETE FROM Seat WHERE ID = @ID";
        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((seat.CinemaRoom * 10000) + (seat.Row * 100) + seat.SeatNr);
            
            Connection.Open();
            wentWell = cmd.ExecuteNonQuery();

        }
        //looks like it already exists in the database!
        catch (SqlException)
        {
            wentWell = -1;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            Connection.Close();
        }
        return wentWell;
    }

    public bool DoesExits(Seat seat)
    {
        bool exits = false;

        List<Seat> check = ReadAll(seat.CinemaRoom);

        foreach (Seat item in check)
        {
            int chechid = (seat.CinemaRoom * 1000)+(seat.Row * 100)+seat.SeatNr;
            int itemid = (item.CinemaRoom * 1000)+(item.Row * 100)+item.SeatNr;
            if (itemid.Equals(chechid))
            {
                exits = true;
            }
        }

        return exits;
    }

    #endregion
}

